package com.supercube.sport.yoga.utils;

import com.supercube.sport.yoga.domain.CardGroupByMemberDTO;
import com.supercube.sport.yoga.reportForms.CourseFormsDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.ClassPathResource;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.LongFunction;
import java.util.stream.Collectors;

/**
 * Description：
 * Created By guowen
 */
@Slf4j
public class ExcelUtils {
    /**
     * 导出excel
     * @param response
     * @param fileName
     * @param dataList
     * @param clas
     * @param <T>
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, String fileName,String titleName, List<T> dataList, Class<?> clas) throws Exception {
        try (OutputStream os = response.getOutputStream()) {
            try (HSSFWorkbook workbook = new HSSFWorkbook()) {
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                HSSFSheet sheet = workbook.createSheet(fileName);

                List<ExcelHeader> headList = getHeadList(clas);
                setImg(workbook, sheet);
                HSSFCellStyle setBorder = setTitleBackGroundStyle(workbook);

                Row firstRow = sheet.createRow(0);
                firstRow.setHeightInPoints(50);
                Cell firstCell = firstRow.createCell(0);
                firstCell.setCellValue(titleName);
                firstCell.setCellStyle(setBorder);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headList.size()-1));
                //一、设置背景色：
                HSSFCellStyle setBorder2 = setTitleBackGroundStyle(workbook);
                setBorder2.setFillForegroundColor(IndexedColors.AQUA.getIndex());// 设置背景色
                setBorder2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                Row titleRow = sheet.createRow(1);
                for (int i = 0; i < headList.size(); i++) {
                    Cell cell = titleRow.createCell(i);
                    cell.setCellValue(headList.get(i).getTitle());
                    cell.setCellStyle(setBorder2);
                    sheet.setColumnWidth(i, 20 * 256);
                }
                for (int i = 0; i < dataList.size(); i++) {
                    Row row = sheet.createRow(i + 2);
                    for (int j = 0; j < headList.size(); j++) {
                        ExcelHeader excelHeader = headList.get(j);
                        Cell cell = row.createCell(j);
                        if(j==0){
                            cell.setCellStyle(setBorder2);
                        }
                        String value = BeanUtils.getProperty(dataList.get(i), excelHeader.getFieldName());
                        cell.setCellValue(value);
                    }
                }
                workbook.write(os);
                os.flush();
            }
        } catch (Exception e) {
            log.error("导出excel异常", e);
        }
    }

    private static void setImg(HSSFWorkbook workbook, HSSFSheet sheet) throws IOException {
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        ClassPathResource jumBo = new ClassPathResource("/static/img/jumBo.png");
        InputStream inputStreamImg = jumBo.getInputStream();
        BufferedImage bufferImg = ImageIO.read(inputStreamImg);

        ImageIO.write(bufferImg, "png", byteArrayOut);
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //anchor主要用于设置图片的属性
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 200, 255, (short) 0, 0, (short) 1, 0);
        anchor.setAnchorType(3);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
    }

    public static <T> void exportExcel2(HttpServletResponse response, String fileName, String titleName, List<Map<String, BigDecimal[]>> dataList, List<String> headList) throws Exception {
        try (OutputStream os = response.getOutputStream()) {
            try (HSSFWorkbook workbook = new HSSFWorkbook()) {
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                HSSFSheet sheet = workbook.createSheet(fileName);
                setImg(workbook, sheet);

                HSSFCellStyle setBorder = setTitleBackGroundStyle(workbook);
                Row firstRow = sheet.createRow(0);
                firstRow.setHeightInPoints(50);
                Cell firstCell = firstRow.createCell(0);
                firstCell.setCellValue(titleName);
                firstCell.setCellStyle(setBorder);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headList.size()-1));
                HSSFCellStyle setBorder2 = setTitleBackGroundStyle(workbook);
                setBorder2.setFillForegroundColor(IndexedColors.AQUA.getIndex());// 设置背景色
                setBorder2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                Row titleRow = sheet.createRow(1);
                for (int i = 0; i < headList.size(); i++) {
                    Cell cell = titleRow.createCell(i);
                    cell.setCellValue(headList.get(i));
                    cell.setCellStyle(setBorder2);
                    sheet.setColumnWidth(i, 20 * 256);
                }
                //  dataList  1：[1,2,3,4]
                //            2：[3,4,5,6]

                for (int i = 0; i < dataList.size(); i++) {
                    Row row = sheet.createRow(i + 2);
                    Map<String, BigDecimal[]> map = dataList.get(i);
                    map.forEach((key,value)->{
                        //第一列
                        Cell cell0 = row.createCell(0);
                        cell0.setCellValue(key);
                        cell0.setCellStyle(setBorder2);
                        BigDecimal[] amounts = value;
                        for (int j = 0; j < amounts.length; j++) {
                            Cell cell = row.createCell(j+1);
                            cell.setCellValue(amounts[j].toString());
                        }
                    });
                }
                workbook.write(os);
                os.flush();
            }
        } catch (Exception e) {
            log.error("导出excel异常", e);
        }
    }
    public static <T> void exportExcel3(HttpServletResponse response, String fileName,String titleName, List<T> dataList, Class<?> clas) throws Exception {
        try (OutputStream os = response.getOutputStream()) {
            try (HSSFWorkbook workbook = new HSSFWorkbook()) {
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                HSSFSheet sheet = workbook.createSheet(fileName);

                List<ExcelHeader> headList = getHeadList(clas);
                setImg(workbook, sheet);
                HSSFCellStyle setBorder = setTitleBackGroundStyle(workbook);

                Row firstRow = sheet.createRow(0);
                firstRow.setHeightInPoints(50);
                Cell firstCell = firstRow.createCell(0);
                firstCell.setCellValue(titleName);
                firstCell.setCellStyle(setBorder);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headList.size()-1));
                //一、设置背景色：
                HSSFCellStyle setBorder2 = setTitleBackGroundStyle(workbook);
                setBorder2.setFillForegroundColor(IndexedColors.AQUA.getIndex());// 设置背景色
                setBorder2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                Row titleRow = sheet.createRow(1);
                for (int i = 0; i < headList.size(); i++) {
                    Cell cell = titleRow.createCell(i);
                    cell.setCellValue(headList.get(i).getTitle());
                    cell.setCellStyle(setBorder2);
                    sheet.setColumnWidth(i, 20 * 256);
                }
                for (int i = 0; i < dataList.size(); i++) {
                    Row row = sheet.createRow(i + 2);
                    for (int j = 0; j < headList.size(); j++) {
                        ExcelHeader excelHeader = headList.get(j);
                        Cell cell = row.createCell(j);
                        if(j==0){
                            cell.setCellStyle(setBorder2);
                        }
                        String value = BeanUtils.getProperty(dataList.get(i), excelHeader.getFieldName());
                        cell.setCellValue(value);
                    }
                }

                fileName= "汇总表";
                HSSFSheet sheetCollect = workbook.createSheet(fileName);
                String [] headListCollect = {"教练","预约人次","签到人次","课程价格","签到率（%）"};
                setImg(workbook, sheetCollect);

                Row firstRowCollect = sheetCollect.createRow(0);
                firstRowCollect.setHeightInPoints(50);
                Cell firstCellCollect = firstRowCollect.createCell(0);
                firstCellCollect.setCellValue(titleName);
                firstCellCollect.setCellStyle(setBorder);
                sheetCollect.addMergedRegion(new CellRangeAddress(0, 0, 0, headListCollect.length-1));

                Row titleRowCollect = sheetCollect.createRow(1);
                for (int i = 0; i < headListCollect.length; i++) {
                    Cell cell = titleRowCollect.createCell(i);
                    cell.setCellValue(headListCollect[i]);
                    cell.setCellStyle(setBorder2);
                    sheetCollect.setColumnWidth(i, 20 * 256);
                }
                List<CourseFormsDTO> clist = (List<CourseFormsDTO>)dataList;
                //去掉合并行
                CourseFormsDTO all = clist.get(clist.size()-1);
                clist.remove(clist.size()-1);
                Map<String, List<CourseFormsDTO>> collect = clist.stream()
                        .collect(Collectors.groupingBy(CourseFormsDTO::getCocahName));
                int i=0;
                for(List<CourseFormsDTO> list : collect.values()){
                    //根据某个属性分组，汇总某个属性
                    String cocahName = list.get(0).getCocahName();
                    int  signNum = list.stream().collect(Collectors.summingInt((dto) -> {
                        //lambda表达式
                        return Integer.parseInt(dto.getSignNum());
                    }));
                    int  surplusNum = list.stream().collect(Collectors.summingInt((dto) -> {
                        //lambda表达式
                        return Integer.parseInt(dto.getSurplusNum());
                    }));
                    int  coursePrice = list.stream().collect(Collectors.summingInt((dto) -> {
                        //lambda表达式
                        return Integer.parseInt(dto.getCoursePrice());
                    }));
                    double  signRate = list.stream().collect(Collectors.summingDouble((dto) -> {
                        //lambda表达式
                        return Double.parseDouble(dto.getSignRate());
                    }));
                    Row row = sheetCollect.createRow(i + 2);
                    Cell cell0 = row.createCell(0);
                    cell0.setCellValue(cocahName);
                    Cell cell1 = row.createCell(1);
                    cell1.setCellValue(signNum+"");
                    Cell cell2 = row.createCell(2);
                    cell2.setCellValue(surplusNum+"");
                    Cell cell3 = row.createCell(3);
                    cell3.setCellValue(coursePrice+"");
                    Cell cell4 = row.createCell(4);
                    cell4.setCellValue(signRate+"");
                    i++;
                }
                Row rowAll = sheetCollect.createRow(i+2);
                Cell cell0 = rowAll.createCell(0);
                cell0.setCellValue("合计");
                Cell cell1 = rowAll.createCell(1);
                cell1.setCellValue(all.getSignNum());
                Cell cell2 = rowAll.createCell(2);
                cell2.setCellValue(all.getSurplusNum());
                Cell cell3 = rowAll.createCell(3);
                cell3.setCellValue(all.getCoursePrice());
                Cell cell4 = rowAll.createCell(4);
                cell4.setCellValue(all.getSignRate());
                i++;
                workbook.write(os);
                os.flush();
            }
        } catch (Exception e) {
            log.error("导出excel异常", e);
        }
    }
    private static HSSFCellStyle setTitleBackGroundStyle(HSSFWorkbook workbook) {
        HSSFCellStyle setBorder = workbook.createCellStyle();
        //自动换行
        setBorder.setWrapText(true);

        //二、设置边框:
        setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        //三、设置居中:
        setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        //垂直居中
        setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 四、设置字体:
        HSSFFont font2 = workbook.createFont();
        font2.setFontName("黑体");
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        font2.setFontHeightInPoints((short) 12);
        setBorder.setFont(font2);//选择需要用到的字体格式
        return setBorder;
    }

    /**
     * 获取表头
     * @param clas
     * @return
     */
    private static List<ExcelHeader> getHeadList(Class<?> clas) {
        List<ExcelHeader> list = new ArrayList<>();
        Field[] fields = clas.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                list.add(new ExcelHeader(excelField.title(), excelField.order(), field.getName()));
            }
        }
        Collections.sort(list);
        return list;
    }
}
